package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.kit.DoubleKit;
import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.AdminSeller;
import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;

public class UserService {
	
	public boolean addUser(User newObject){
		return newObject.save();
	}
	/**
	 * 向第三方写入会员信息。
	 */

//	public int saveVip(String card,String aname,String aphone,String date,String storeNo,String ssx) {
//		return Db.use("sqlserver").update("insert into VipCards (CardNo,HandOutDate,ValidDate,HolderName,HolderTel,MakeDate,ClearDate,HandOutDept,CardTypeID,sex) values(?,?,?,?,?,?,?,?,?,?)", card,date,"2099-12-31",aname,aphone,date,date,storeNo,1,ssx);
//	}
//	public Record findVipId(String aphone) {
//		return Db.use("sqlserver").findFirst("select CardID from VipCards where HolderTel=?",aphone);
//	}
//	public int saveVipSum(Integer id, String time) {
//		return Db.use("sqlserver").update("insert into VIPIntegralSummary (CardID,LastTime) values(?,?)",id,time);
//	}
//	public void setPhone(String phone,String oldPhone){
//		Db.use("sqlserver").update("update VipCards set HolderTel=? where HolderTel=?",phone,oldPhone);
//	}
	public List<Store> findStoreByIds(String store) {
		return Store.me().find("select title from t_dict_store where storeid in ('"+store+"')");
	}
	public User findById(int id){
		return User.me().findFirst("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount "
				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid left join (select userid,count(id) as countcum,sum(amount) as allCount from "
				+ " t_bus_order where orderState=2 group by userid) C on C.userid= B.id WHERE A.id=?",id);
	}
	public Page<Record> findByPage(int pageNum, int pageSize, String phone, String card, String sBeginDate, String sEndDate, String taici, String state,int toBossOneId, int toBossTwoId, int toBossThreeId){
			StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
					+ "left join t_bus_user G on G.id=A.inviteid "
					+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
					+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
					+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
					+ "left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)CNum on  CNum.inviteid= A.id  "
					+ "left join (select userid,count(id) as cou,sum(amount) as allCount from  t_bus_order where orderState NOT in(1,5,7,9) group by userid)D on D.userid= A.id where 1=1 ");
			List<Object> params = new ArrayList<Object>();
			//根据登录用户所属营业部
			if(toBossOneId != 0){
				sBuffer.append( "and A.toBossOneId=? ");
				params.add(toBossOneId);
			}
			//根据登录用户所属分公司
			if(toBossTwoId != 0){
				sBuffer.append( "and A.toBossTwoId=? ");
				params.add(toBossTwoId);
			}
			//根据登录用户所属公司
			if(toBossThreeId != 0){
				sBuffer.append( "and A.toBossThreeId=? ");
				params.add(toBossThreeId);
			}
			if(StrKit.notBlank(phone)){
				sBuffer.append(" AND A.phone like ?");
				params.add("%"+phone+"%");
			}
			if(StrKit.notBlank(card)){
				sBuffer.append(" and A.card=? ");
				params.add(card);
			}
	/*		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
				sBuffer.append(" AND A.createDate BETWEEN ? AND ?");
				params.add(beginDate);
				params.add(endDate);
			}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
				params.add(beginDate);
			}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
				params.add(endDate);
			}*/
			if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
				sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
				params.add(sBeginDate);
				params.add(sEndDate);
			}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
				sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
				params.add(sBeginDate);
			}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
				sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
				params.add(sEndDate);
			}
			/*if(StrKit.notBlank(address)){
				sBuffer.append(" AND A.address like ?");
				params.add("%"+address+"%");
			}
			if(StrKit.notBlank(title)){
				sBuffer.append(" and A.storeName in ('"+title+"')");
			}*/
			/*if(StrKit.notBlank(adId)){
				sBuffer.append(" and A.toBossOneId in ('"+adId+"')");
			}*/
			if(StrKit.notBlank(taici)){
				sBuffer.append(" and A.taici in ('"+taici+"')");
			}
			if(StrKit.notBlank(state)){
				sBuffer.append(" and A.state in ('"+state+"')");
			}

			sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
			return Db.paginate(pageNum, pageSize, "SELECT A.* ,ifnull(D.cou, 0) AS countnum, CNum.userNum ,G.id AS pareId, G.name AS pareName, S.trueName,ST.title AS storeName", sBuffer.toString(),params.toArray());
		}
	/**
	 * 查询团队详情
	 * @param userid
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param card
	 * @param address
	 * @param title
	 * @param dao
	 * @param taici
	 * @param state
	 * @param storeid
	 * @param toBossOneId
	 * @param toBossTwoId
	 * @param toBossThreeId
	 * @return
	 */
	public Page<Record> findByPageteam(int id, int pageNum, int pageSize, String card, String phone, String taici, String state, int toBossOneId, int toBossTwoId, int toBossThreeId){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_bus_user G on G.id=A.inviteid "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ " left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)CNum on  CNum.inviteid= A.id  "
				+ "left join (select userid,count(id) as cou,sum(amount) as allCount from  t_bus_order where orderState NOT in(1,5,7,9) group by userid)D on D.userid= A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		//根据登录用户所属营业部
		if(toBossOneId != 0){
			sBuffer.append( "and A.toBossOneId=? ");
			params.add(toBossOneId);
		}
		//根据登录用户所属分公司
		if(toBossTwoId != 0){
			sBuffer.append( "and A.toBossTwoId=? ");
			params.add(toBossTwoId);
		}
		//根据登录用户所属公司
		if(toBossThreeId != 0){
			sBuffer.append( "and A.toBossThreeId=? ");
			params.add(toBossThreeId);
		}
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}
		sBuffer.append(" and A.inviteid in ('"+id+"')");
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.paginate(pageNum, pageSize, "SELECT A.* ,ifnull(D.cou, 0) AS countnum, CNum.userNum ,G.id AS pareId, G.name AS pareName, S.trueName,ST.title AS storeName", sBuffer.toString(),params.toArray());
	}
	
	public boolean findExistsByUsername(String username){
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE username=?",username)!=null;
	}
	
	public boolean findExistsByUsernameWithout(String username,int userid){
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE username=? AND id!=?",username,userid)!=null;
	}

	public User findByPhone(String phone) {
		return User.me().findFirst("select id from t_bus_user where phone=?",phone);
	}
	/**
	 * 获取公司表的id
	 * @param UserId
	 * @return
	 */
	public Seller findSellerByUserId(int UserId) {
		return Seller.me().findFirst("select * from t_bus_seller where userid=?",UserId);
	}
	
	/**
	 * 根据storeid查询store
	 * @param store
	 * @return
	 */
	public Store findStoreById1(String store) {
		return Store.me().findFirst("select id from t_dict_store where storeid=?",store);
	}
	public List<Store> findStoreList(String sellerId) {
		return Store.me().find("select A.storeid,A.title from t_dict_store A  where A.sellerId =?",sellerId);
	}
	public List<Store> findStoreidList(String storeid) {
		return Store.me().find("select storeid,title,shortName from t_dict_store  where storeid = ?",storeid);
	}
	public List<Store> findStoreList() {
		return Store.me().find("select * from t_dict_store");
	}
	/**
	 * 根据商家的userid,获取商家下的所有的分公司storeid，title
	 * @param userid
	 * @return
	 */
	public List<Store> findStoreList(int userid) {
		return Store.me().find("select A.storeid,A.title,A.shortName from t_dict_store A INNER JOIN t_bus_seller as B ON A.sellerId=B.sellerId where B.userid=?",userid);
	}
	/**
	 * 根据联系人或店员的userid,获取所有的分公司storeid，title
	 * @param userid
	 * @return
	 */
	public List<Store> findOneStore(int userid) {
		return Store.me().find("select A.storeid,A.title,A.shortName from t_dict_store A INNER JOIN t_auth_admin_seller as B ON A.storeid=B.storeid where B.id=?",userid);
	}
	/**
	 * 根据用户id获取商家的sellerid
	 * @param userid
	 * @return
	 */
	public Record findByUserId(int userid){
		return Db.findFirst("select sellerId from t_bus_seller where userid=?",userid);
	}
	/**
	 * 根据用户id获取分公司的storeid
	 * @param userid
	 * @return
	 */
	public Record findBystroeid(int userid){
		return Db.findFirst("select storeid  from t_auth_admin_seller where id=?",userid);
	}
	/**
	 * 分公司Id查所属公司
	 * @param storeid
	 * @return
	 */
	public Record findBysellerId(String storeid){
		return Db.findFirst("select sellerId from t_dict_store where storeid = ?",storeid);
	}
	/**
	 * 根据userid,获取角色
	 * @param userid
	 * @return
	 */
	public int findByRoleId(int userid){
		return Db.queryInt("SELECT C.id  from t_auth_admin_seller as A inner JOIN  t_auth_admin_roles_seller as B ON A.id=B.userid  INNER JOIN t_auth_roles_seller AS C ON B.roleid = C.id where B.userid=?" ,userid);
	}
	
	/**
	 * 根据登录id获取用户角色ID
	 * @param userid
	 * @return
	 */
	public int findRoleidByAdminSellerId(int userid){
		return Db.queryInt("SELECT roleid  from t_auth_admin_roles_seller where userid = ?" ,userid);
	}
	
	/**
	 * 通过角色是分公司时，找到营的id
	 * @param adminSellerId
	 * @return
	 */
	public int findStoreId(String storeid){
		return Db.queryInt("SELECT id  from t_dict_store where storeid = ?" ,storeid);
	}
	
	/**
	 * 通过角色是公司时，找到公司的id
	 * @param adminSellerId
	 * @return
	 */
	public int findSellerid(int adminSellerId){
		return Db.queryInt("SELECT id  from t_bus_seller where userid = ?" ,adminSellerId);
	}
	/**
	 * 根据userid,获取用户所属厂家id
	 * @param userid
	 * @return
	 */
	public int findUsertoBossThreeId(int userid){
		return Db.queryInt("SELECT toBossThreeId  from t_bus_user where id = ?" ,userid);
	}
	
	/**
	 * 对会员进行判定
	 * @param userid
	 * @return
	 */
	public Record findByVipRole(int userid){
		return Db.findFirst("SELECT VipPower from t_auth_admin_seller  where id=?" ,userid);
	}
	/**
	 * 根据用户id查看是否有创建会员的权限
	 * @param userid
	 * @return
	 */
	/*public int findByVip1(int userid){
		return Db.queryInt("select  B.VipPower from  t_dict_store A inner join t_bus_seller B on A.sellerId=B.sellerId "
				+ " left join t_auth_admin_seller C on C.storeid=A.storeid where C.id= ?" ,userid);
	}*/
	/**
	 * 选择营业部
	 * @param store
	 * @return
	 */
	public List<Record> chooseDao(String store) {
		return Db.find("select A.id,A.trueName from t_auth_admin_seller as A "
				+ "inner JOIN t_auth_admin_roles_seller as S on A.id = S.userid where 1=1 and S.roleid = 4 and A.storeid =?",store);
	}

	public Store findStoreById(String store) {
		return Store.me().findFirst("select storeid,id,title,BranchId from t_dict_store where storeid=?",store);
	}
	

	/**
	 * 
	 * @param store
	 * @return
	 */
	public Record findRecordById(String store) {
		return Db.findFirst("select B.id, A.title from t_dict_store as A inner JOIN  t_auth_admin_seller as B ON A.storeid=B.storeid where A.storeid=?",store);
	}

	public User findUserById(int id) {
		return User.me().findFirst("select *,(year(now())-year(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age from t_bus_user where id=?",id);
	}

	public List<Record> findUserList(String store) {
		return Db.find("select trueName from t_auth_admin_seller A left join t_dict_store B on A.storeid=B.storeid where B.title=?",store);
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "会员信息导出", titles, listz);
	}
	public void exportVip(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "会员积分统计", titles, listz);
	}

	public List<Record> findExcelByPage(String phone, String card, String sBeginDate, String sEndDate, String beginDate,
			String endDate,String address,String title, String dao, String taici, String state, String storeid,String sellerId) {

		
		
/*		StringBuffer sBuffer = new StringBuffer("SELECT A.* ,ifnull(E.cou, 0) AS countnum ,ifnull(C.countcum,0) excnum ,ifnull(C.allCount,0) allCount "
				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid "
				+ "left join t_dict_store D on D.title=A.storeName "
				+ "left join t_dict_store F on F.storeid=D.storeid "
				+ " left join (select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where "
				+ " orderState=2 group by userid) C on C.userid= B.id where 1=1 ");*/
		StringBuffer sBuffer = new StringBuffer("SELECT A.*, ifnull(C.countcum, 0) AS countnum,CNum.userNum ,G.id AS pareId, G.name AS pareName, S.trueName"
				+ ", SE.sellerName AS sellerName, ST.title AS storeName FROM t_bus_user A "
				+ "left join t_bus_user G on G.id=A.inviteid "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join (select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where orderState NOT in(1,5,7,9)  group by userid) C on C.userid= A.id "
				+ " left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)CNum on  CNum.inviteid= A.id  "
				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append( "and A.toBossThreeId=?");
		params.add(findselleridBysellerId(sellerId).get("id"));
		if(StrKit.notBlank(storeid)){
			sBuffer.append(" and D.storeid=?");
			params.add(storeid);
		}
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND A.createDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(StrKit.notBlank(address)){
			sBuffer.append(" AND A.address like ?");
			params.add("%"+address+"%");
		}
		if(StrKit.notBlank(title)){
			sBuffer.append(" and A.storeName in ('"+title+"')");
		}
		if(StrKit.notBlank(dao)){
			sBuffer.append(" and A.toBossOneId in ('"+dao+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}

		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find( sBuffer.toString(),params.toArray());
	}

	public Page<Record> findByVipPage(int pageNum, int pageSize, String phone, String card,int toBossTwoId ,int toBossThreeId,int toBossOneId ) {
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ "where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		//根据登录用户营业部
		if(toBossOneId != 0){
			sBuffer.append( "and A.toBossOneId=? ");
			params.add(toBossOneId);
		}
		//根据登录用户所属分公司
		if(toBossTwoId != 0){
			sBuffer.append( "and A.toBossTwoId=? ");
			params.add(toBossTwoId);
		}
		//根据登录用户所属公司
		if(toBossThreeId != 0){
			sBuffer.append( "and A.toBossThreeId=? ");
			params.add(toBossThreeId);
		}
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.paginate(pageNum, pageSize, "SELECT A.* ,ST.title AS storeName", sBuffer.toString(),params.toArray());
	}
	public Page<Record> findByVipPages(int pageNum, int pageSize, String phone, String card, int date,int toBossTwoId ,int toBossThreeId,int toBossOneId ) {
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		//根据登录用户营业部
		if(toBossOneId != 0){
			sBuffer.append( "and A.toBossOneId=? ");
			params.add(toBossOneId);
		}
		//根据登录用户所属分公司
		if(toBossTwoId != 0){
			sBuffer.append( "and A.toBossTwoId=? ");
			params.add(toBossTwoId);
		}
		//根据登录用户所属公司
		if(toBossThreeId != 0){
			sBuffer.append( "and A.toBossThreeId=? ");
			params.add(toBossThreeId);
		}
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		switch (date) {
			case 1://商品
				sBuffer.append(" ORDER BY A.sppoint DESC,A.id desc");
				break;
			case 2://健康
				sBuffer.append(" ORDER BY A.jkpoint DESC,A.id desc");
				break;
			case 3://教育
				sBuffer.append(" ORDER BY A.jypoint DESC,A.id desc");
				break;
			default:
				sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
				break;
		}
	
		return Db.paginate(pageNum, pageSize, "SELECT A.* ,ST.title as storeName", sBuffer.toString(),params.toArray());
	}
	public List<Record> findExcelVipByPage(String phone, String card, String title,String sellerId) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,ST.title as storeName FROM t_bus_user A left join t_dict_store ST on ST.id=A.toBossTwoId where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append( "and A.toBossThreeId=?");
		params.add(findselleridBysellerId(sellerId).get("id"));
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(title)){
			sBuffer.append(" and ST.title=?");
			params.add(title);
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find(sBuffer.toString(),params.toArray());
	}

	public List<Record> findOffJfOrderList(String phone) {
		return Db.find("select * from t_vip_user_orderdail where phone=?",phone);
	}

	public List<Record> findOrderList(int id) {
		return Db.find("select A.*,D.title from t_bus_user_value_log A  "
				+ "left join t_bus_user C on C.id=A.userid left join t_dict_store D on A.storeid=D.storeid where C.id=?",id);
	}

	public List<Record> findMesageList(String phone, String card, String sBeginDate,String sEndDate, String beginDate,
			String endDate, String title, String dao, String taici, String state) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount,"
				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid left join "
				+ "(select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where orderState=2 group by userid) C on C.userid= B.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND A.createDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(StrKit.notBlank(title)){
			sBuffer.append(" and A.storeName in ('"+title+"')");
		}
		if(StrKit.notBlank(dao)){
			sBuffer.append(" and A.toBossOneId in ('"+dao+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}

		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find(sBuffer.toString(),params.toArray());
	}
	/**
	 * 生产期
	 * @param times
	 * @return
	 */
	public List<User> findByTime(String times) {
		return User.me().find("select * from t_bus_user where state=2 and babythday=? ",times);
	}
	public List<AdminSeller> findSeller() {
		return AdminSeller.me().find("select * from t_auth_admin_seller ");
	}
	/**
	 * 查询所有营业部
	 * @return
	 */
	/*
	public List<AdSeller> findAdSeller() {
		return AdSeller.me().find("select * from t_auth_admin_seller");
	}
	*//**
	 * 按照名字查询当前admin
	 * @return
	 */
	public AdminSeller findAdSellerByName(String dao) {
		return AdminSeller.me().findFirst("select * from t_auth_admin_seller where trueName = ?",dao);
	}
	/**
	 * 按照当前所属分公司ID查询分公司
	 * @return
	 */
	public Store findStoreBytoBossTwoId(int dao) {
		return Store.me().findFirst("select * from t_dict_store where id = ?",dao);
	}
	/**
	 * 按照userid查询当前公司的id
	 * @return
	 */
	public Seller findSellerByAdminId(int userid) {
		return Seller.me().findFirst("select * from t_bus_seller where userid = ?",userid);
	}
	/**
	 * 根据storeid,获取用户所属厂家id
	 * @param userid
	 * @return
	 */
	public Store findSellerByStoreid(String storeid){
		return Store.me().findFirst("SELECT *  from t_dict_store where storeid = ?" ,storeid);
	}
	/**
	 * 根据sellerid,获取用户所属厂家
	 * @param userid
	 * @return
	 */
	public Seller findselleridBysellerId(String sellerid){
		return Seller.me().findFirst("SELECT *  from t_bus_seller where sellerId = ?" ,sellerid);
	}
	/**
	 * 根据登陆用户的id获取登陆用户的手机号码
	 * @param userid
	 * @return
	 */
	public AdminSeller findAdminSellerByid(int userid){
		return AdminSeller.me().findFirst("SELECT * from t_auth_admin_seller where id = ?" ,userid);
	}
	/**
	 * 根据登陆用户手机号码查询前端用户
	 * @param userid
	 * @return
	 */
	public User findUserByPhone(String phone){
		return User.me().findFirst("SELECT *  from t_bus_user where phone = ?" ,phone);
	}
	/**
	 * 根据登陆用户的前端id查询变动日志
	 * @param userid
	 * @return
	 */
	public User findUserLog(String phone){
		return User.me().findFirst("SELECT *  from t_bus_user where phone = ?" ,phone);
	}
	/**
	  *查询最近七天内变动惠券
	  * @param createDate
	  * @return
	  */
	 public Record queryViablejypoint(int userId,Date createDate) {
	  String sql="select SUM(coupon) as viablejypoint from t_bus_user_value_log b  "
	    + "where b.createDate > ? and b.userid=? and type=1 "
	    + "and  fromOrderNum not in "
	    + "(select fromOrderNum from t_bus_user_value_log where createDate > ? and userid =? and flag=16)";
	  return Db.findFirst(sql, createDate,userId,createDate,userId);
	 } 
	 /**
	 * 根据登陆用户的前端id查询变动日志
	 * @param userid
	 * @return
	 */
	public String findSysConfig(){
		return Db.queryStr("SELECT value  from t_sys_config where code = 'S0014' " );
	}
	/**
	  * 惠券转换余额
	  * @param user
	  * @param jypoint
	  * @param money
	  * @param re
	  * @return
	  * @throws Exception
	  */
	 @Before(Tx.class)
	 public boolean exchangeMoney(User user,Double jypoint,Double money,String orderNumStore,Record re,int type) throws Exception {
		//更改信息
		Record record=new Record();
		record.set("money", money).set("jypoint", jypoint).set("state", 1).set("userId", user.getInt("id")).set("orderNo", orderNumStore);
		if(Db.save("t_bus_user_changeToMoney", record)){
			int count = updateValue(user,0.00,0.00,jypoint,0.00,money,re,type);
			if (count==1) {
				return true;
			}else {
				return false;
			}
		}else{
			throw new Exception("转换失败");
		}
	} 
	 /**
	  * 更改用户积分,抵扣券，惠券并记录
	  * @param user 用户
	  * @param spoint 本次变动积分
	  * @param dedu 本次变动抵扣券
	  * @param coupon 本次变动惠券
	  * @param ecoupon 本次变动电子券
	  * @param money 本次变动金额
	  * @param re 本次记录
	  * @return1 成功  2保存财富日志表失败 3 更改用户财富失败
	  * @throws Exception
	  */
	@Before(Tx.class)
	public int updateValue(User user,Double spoint,Double dedu,Double coupon,Double ecoupon,Double money, Record re,int type) throws Exception {
		//更改前的值
		Double sppointHistory = user.getDouble("sppoint");
		Double deduHistory = user.getDouble("jkpoint");
		Double couponHistory = user.getDouble("jypoint");
		Double ecouponHistory = user.getDouble("ecoupon");
		//更改后
		Double newSpoint = 0.00;
		Double newJkpoint = 0.00;
		Double newJypoint = 0.00;
		Double newEcoupon = 0.00;
		if (type==1) {
			newSpoint = DoubleKit.add(sppointHistory, spoint);
			newJkpoint = DoubleKit.add(deduHistory, dedu);
			newJypoint = DoubleKit.add(couponHistory, coupon);
			newEcoupon = DoubleKit.add(ecouponHistory, ecoupon);
		}else {
			newSpoint = DoubleKit.sub(sppointHistory, spoint);
			newJkpoint = DoubleKit.sub(deduHistory, dedu);
			newJypoint = DoubleKit.sub(couponHistory, coupon);
			newEcoupon = DoubleKit.add(ecouponHistory, ecoupon);
		}
	  
		//修改
		user.set("sppoint", newSpoint);
		user.set("jkpoint", newJkpoint);
		user.set("jypoint", newJypoint);
		user.set("ecoupon", newEcoupon);
		if(user.update()){
		//记录
		re.set("createDate", new Date())
		.set("sppoint", spoint)
		.set("sppointHistory", sppointHistory)
		.set("dedu", dedu)
		.set("deduHistory", deduHistory)
		.set("coupon", coupon)
		.set("couponHistory", couponHistory)
		.set("ecoupon", ecoupon)
		.set("ecouponHistory", ecouponHistory)
		.set("orderMoney", money);
		if(!Db.save("t_bus_user_value_log", re)){
			throw new Exception("保存财富日志表失败");
		}
			return 1;
		}
		throw new Exception("更改用户财富失败");
	}
}
